package com.erp.app.catalog.itemMaster.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

import com.erp.app.catalog.itemMaster.bizLogic.ItemMasterGroupCommonUtil;
import com.erp.app.catalog.itemMaster.criteria.ItemMasterCriteria;
import com.erp.app.catalog.itemMaster.criteria.ItemMasterDtlCriteria;
import com.erp.app.catalog.itemMaster.vo.ItemMasterDtlVO;
import com.erp.app.catalog.itemMaster.vo.ItemMasterVO;
import com.erp.app.common.ProjectConstants;
import com.erp.framework.exceptions.FindRecordException;
import com.erp.framework.model.criteria.SimpleBaseSrchCriteria;
import com.erp.framework.model.vo.SimpleBaseVo;
import com.erp.framework.util.CommonUtil;
import com.erp.framework.util.logger.Logger;

public class ItemMasterDtlGroupCommonDao {
	ItemMasterGroupCommonUtil util = new ItemMasterGroupCommonUtil();
	
	public static String getSqlQuery(SimpleBaseSrchCriteria obj) {
		ItemMasterDtlCriteria criteria = (ItemMasterDtlCriteria)obj;
		String sql = "";
		
		String whereClause = toWhereClause(criteria);
		String groupByClause = "";
		String orderByClause = "";
		sql = " SELECT DISTINCT itme_dtl.ITEM_ID, itme_dtl.ITEM_DETAIL_ID, "
			+ " itme_dtl.ITEM_DETAIL_UNIT_OF_MEASURE, itme_dtl.ITEM_RECURSIVE_LINE_SEQUENCE, "
			+ " itme_dtl.ITEM_DETAIL_TYPE, itme_dtl.SEQUENCE_ORDER, itme_dtl.AUDIT_ID, "
			+ " itme_dtl.HIDDEN_FIELD, itme_dtl.ACTIVE, "
			+ " DECODE(NVL(itme_dtl.ITEM_RECURSIVE_LINE_SEQUENCE,-1),-1,itme_dtl.SEQUENCE_NO, "
			+ " SF_GET_RECDTL_ID(itme_dtl.ITEM_ID,itme_dtl.SEQUENCE_NO)) SEQUENCE_NO ,"
			+ " DECODE(NVL(itme_dtl.ITEM_RECURSIVE_LINE_SEQUENCE,- 1), "
			+ "- 1,DECODE(itme_dtl.ITEM_DETAIL_TYPE,'W',NVL((SELECT REF_DESC " 
            + " FROM   T_OPTION_CODE_DTL WHERE  COMPANY_CODE = 'KS' " 
            + " AND REF_MASTER_CODE = itme_dtl.ITEM_DETAIL_OPTION_CODE " 
            + " AND REF_CODE = itme_dtl.ITEM_DETAIL_VALUE),''),itme_dtl.ITEM_DETAIL_VALUE)," 
            + " SF_GET_RECDTL_DESC(itme_dtl.ITEM_ID,itme_dtl.SEQUENCE_NO)) " 
            + " ||DECODE(NVL(itme_dtl.ITEM_DETAIL_UNIT_OF_MEASURE,'-1'),'-1',' ', " 
            + " DECODE(NVL(itme_dtl.ITEM_DETAIL_VALUE,'-1'),'-1','',(SELECT ' '||code_dtl.REF_DESC " 
            + " FROM T_CODE_DTL code_dtl WHERE  code_dtl.COMPANY_CODE = 'KS' " 
	        + " AND code_dtl.REF_MASTER_CODE = 'UOM' " 
	        + " AND code_dtl.REF_CODE = itme_dtl.ITEM_DETAIL_UNIT_OF_MEASURE))) DTL_VALUES "
	        + " FROM T_ITEM_DETAIL itme_dtl "
			+ whereClause
			+ groupByClause
			+ orderByClause;
		Logger.log(sql);
		return sql;
	}
	public static SimpleBaseVo getSearchResultSetValues(ResultSet rs) throws SQLException {
		
		ItemMasterDtlVO vo = new ItemMasterDtlVO();
		
		vo.setItemId(CommonUtil.isEmpty(rs.getBigDecimal("ITEM_ID"))?"":rs.getBigDecimal("ITEM_ID").toString());
		vo.setItemDtlId(CommonUtil.isEmpty(rs.getBigDecimal("ITEM_DETAIL_ID"))?"":rs.getBigDecimal("ITEM_DETAIL_ID").toString());
		vo.setItemDtlUnitMeasure(rs.getString("ITEM_DETAIL_UNIT_OF_MEASURE"));
		vo.setItemRecursiveLineSequence((CommonUtil
				.isEmpty(rs.getBigDecimal("ITEM_RECURSIVE_LINE_SEQUENCE")))?"":rs.getBigDecimal("ITEM_RECURSIVE_LINE_SEQUENCE").toString());
		vo.setItemDtlType(rs.getString("ITEM_DETAIL_TYPE"));
		vo.setSeqOrder(CommonUtil.isEmpty(rs.getBigDecimal("SEQUENCE_ORDER"))?"":rs.getBigDecimal("SEQUENCE_ORDER").toString());
		vo.setAuditId(rs.getString("AUDIT_ID"));
		vo.setHiddenField(rs.getString("HIDDEN_FIELD"));
		vo.setActive(rs.getString("ACTIVE"));
		vo.setSeqNo(CommonUtil.isEmpty(rs.getBigDecimal("SEQUENCE_NO"))?"":rs.getBigDecimal("SEQUENCE_NO").toString());
		vo.setDtlValues(rs.getString("DTL_VALUES"));		
		
		return vo;
	}
	
	public static String toWhereClause(ItemMasterDtlCriteria criteria) {
		StringBuffer whereClause = new StringBuffer();		
		
        if (!CommonUtil.isEmpty(criteria.getItemId())) {
            whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
            whereClause.append(" ITME_DTL.ITEM_ID = '" + criteria.getItemId() + "' ");
        }
        
		return whereClause.toString();    	
	}
	
	public Collection findByCriteria(Object obj, Connection conn) throws FindRecordException {
        // set the search criteria class
        SimpleBaseSrchCriteria criteria = (SimpleBaseSrchCriteria) obj;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList rslt = new ArrayList();

        try {
            String query = getSqlQuery(criteria);
            stmt = conn.createStatement();
            stmt.setMaxRows(ProjectConstants.DB_ROW_NUM);
            rs = stmt.executeQuery(query);

            if (rs != null) {
                while (rs.next()) {
                    rslt.add(getSearchResultSetValues(rs));
                }
            }
        } catch (Exception e) {
            Logger.logExceptions(this, e);
            throw new FindRecordException(e.getMessage());
        } finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (Exception e) {
                Logger.logExceptions(this, e);
            }
            try {
                if (stmt != null)
                    stmt.close();
            } catch (Exception e) {
                Logger.logExceptions(this, e);
            }
        }
        return rslt;
    }
}
